﻿CREATE TRIGGER tr_Verification_Insert_MenuID
ON dbo.t_Verification
FOR INSERT
AS

DECLARE @MID varchar(100),@PMID varchar(100)

SELECT @MID = i.MenuID,@PMID = v.MenuID FROM inserted i	INNER JOIN t_Verification v ON i.ParentID = v.ID

IF EXISTS(SELECT * FROM inserted i
					INNER JOIN t_Verification v ON i.ParentID = v.ID
					WHERE v.MenuID <> i.MenuID AND ISNULL(i.MenuID,0) <> 0)
BEGIN
	ROLLBACK TRAN
	SELECT @MID = MenuPath FROM v_Menu WHERE MenuID = @MID
	SELECT @PMID = MenuPath FROM v_Menu WHERE MenuID = @PMID
	RAISERROR('Menu of the child [%s] must be the same as it''s parent[%s}.',16,1,@MID,@PMID)
	RETURN
END

IF EXISTS(SELECT * FROM inserted WHERE ParentID = 0 AND ISNULL(MenuID,0) = 0 )BEGIN
	ROLLBACK TRAN
	RAISERROR('MenuID must be specified when ParentID is 0.',16,1)
	RETURN
END

IF EXISTS(SELECT * FROM inserted WHERE ISNULL(MenuID,0) = 0)
	UPDATE t_Verification SET MenuID =
	(SELECT p.MenuID FROM inserted i INNER JOIN t_Verification p ON p.ID = i.ParentID)
	WHERE ID = (SELECT ID FROM inserted)

